"""
通用查询服务
支持基于过滤器的通用数据库查询功能
"""

from typing import List, Dict, Any, Optional, Union
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, not_, func, text
from sqlalchemy.sql import select
from sqlalchemy import Table, MetaData

from src.models.generic_filter import (
    GenericFilter, Condition, FilterGroup, Operator, LogicalOperator, SortOrder, SortField, QueryResponse
)
from src.services.query_condition_parser import QueryConditionParser, parse_to_generic_filter
from src.services.query_cache_service import query_cache_service
from src.utils.logging import get_logger
from fastapi import HTTPException, status

logger = get_logger(__name__)


class GenericQueryService:
    """通用查询服务类"""
    
    @staticmethod
    def query_table(
        db: Session, 
        table_name: str, 
        filter_obj: GenericFilter
    ) -> QueryResponse:
        """通用表查询方法"""
        # 构建查询参数用于缓存键
        query_params = {
            "filter": filter_obj.model_dump() if hasattr(filter_obj, 'model_dump') else filter_obj.dict(),
            "table_name": table_name
        }
        
        # 尝试从缓存获取结果
        cached_result = query_cache_service.get_query_result(table_name, query_params)
        if cached_result is not None:
            return QueryResponse(**cached_result)
        
        try:
            # 验证表是否存在
            if not GenericQueryService._table_exists(db, table_name):
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail=f"表 '{table_name}' 不存在"
                )
            
            # 获取表对象
            metadata = MetaData()
            table = Table(table_name, metadata, autoload_with=db.get_bind())
            
            # 构建查询
            query = select(table)
            
            # 应用过滤条件
            if filter_obj.conditions:
                filter_condition = GenericQueryService._build_filter_condition(
                    table, filter_obj.conditions
                )
                if filter_condition is not None:
                    query = query.where(filter_condition)
            
            # 应用搜索条件
            if filter_obj.search and filter_obj.search_fields:
                search_condition = GenericQueryService._build_search_condition(
                    table, filter_obj.search, filter_obj.search_fields
                )
                if search_condition is not None:
                    query = query.where(search_condition)
            
            # 应用排序
            if filter_obj.sort_by:
                order_by_clauses = GenericQueryService._build_order_by(
                    table, filter_obj.sort_by
                )
                query = query.order_by(*order_by_clauses)
            
            # 获取总数（如果请求）
            total = None
            if filter_obj.include_total:
                count_query = select(func.count()).select_from(table)
                if filter_obj.conditions:
                    count_condition = GenericQueryService._build_filter_condition(
                        table, filter_obj.conditions
                    )
                    if count_condition is not None:
                        count_query = count_query.where(count_condition)
                if filter_obj.search and filter_obj.search_fields:
                    search_condition = GenericQueryService._build_search_condition(
                        table, filter_obj.search, filter_obj.search_fields
                    )
                    if search_condition is not None:
                        count_query = count_query.where(search_condition)
                total = db.execute(count_query).scalar()
            
            # 应用分页
            skip = (filter_obj.page - 1) * filter_obj.page_size
            query = query.offset(skip).limit(filter_obj.page_size)
            
            # 执行查询 - 使用原始SQL避免数据类型转换问题
            # 获取查询参数
            compiled = query.compile()
            params = compiled.params
            
            # 构建原始SQL查询字符串
            sql_str = str(compiled)
            
            # 使用原始SQL查询，避免SQLAlchemy的类型转换问题
            result = db.execute(text(sql_str), params)
            data = []
            for row in result:
                # 手动构建字典
                row_dict = {}
                columns = result.keys()
                for i, column in enumerate(columns):
                    row_dict[column] = row[i]
                data.append(row_dict)
            
            # 计算总页数
            total_pages = None
            if total is not None:
                total_pages = (total + filter_obj.page_size - 1) # filter_obj.page_size
            
            response = QueryResponse(
                data=data,
                total=total,
                page=filter_obj.page,
                page_size=filter_obj.page_size,
                total_pages=total_pages
            )
            
            # 缓存查询结果
            query_cache_service.set_query_result(table_name, query_params, response.model_dump())
            
            return response
            
        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"查询表 {table_name} 失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail=f"查询失败: {str(e)}"
            )
    
    @staticmethod
    def _build_filter_condition(table, conditions: List[Union[Condition, FilterGroup]], group_operator=LogicalOperator.AND) -> Any:
        """构建过滤条件"""
        if not conditions:
            return None
        
        conditions_list = []
        for condition in conditions:
            if isinstance(condition, Condition):
                cond = GenericQueryService._build_single_condition(table, condition)
                if cond is not None:
                    conditions_list.append(cond)
            elif isinstance(condition, FilterGroup):
                # 递归构建子条件，使用组的操作符
                group_cond = GenericQueryService._build_filter_condition(
                    table, condition.conditions, condition.operator
                )
                if group_cond is not None:
                    conditions_list.append(group_cond)
        
        if not conditions_list:
            return None
        
        # 根据操作符组合条件
        if group_operator == LogicalOperator.OR:
            return or_(*conditions_list)
        else:
            return and_(*conditions_list)
    
    @staticmethod
    def _build_single_condition(table, condition: Condition) -> Any:
        """构建单个条件"""
        column = table.c.get(condition.field)
        if column is None:
            logger.warning(f"列 '{condition.field}' 在表 '{table.name}' 中不存在")
            return None
        
        operator = condition.operator
        value = condition.value
        
        if operator == Operator.EQUALS:
            return column == value
        elif operator == Operator.NOT_EQUALS:
            return column != value
        elif operator == Operator.GREATER_THAN:
            return column > value
        elif operator == Operator.GREATER_THAN_OR_EQUAL:
            return column >= value
        elif operator == Operator.LESS_THAN:
            return column < value
        elif operator == Operator.LESS_THAN_OR_EQUAL:
            return column <= value
        elif operator == Operator.IN:
            return column.in_(value)
        elif operator == Operator.NOT_IN:
            return column.notin_(value)
        elif operator == Operator.LIKE:
            return column.like(f"%{value}%")
        elif operator == Operator.ILIKE:
            return column.ilike(f"%{value}%")
        elif operator == Operator.IS_NULL:
            return column.is_(None)
        elif operator == Operator.IS_NOT_NULL:
            return column.isnot(None)
        elif operator == Operator.BETWEEN:
            if isinstance(value, list) and len(value) == 2:
                return column.between(value[0], value[1])
        elif operator == Operator.CONTAINS:
            return column.contains(value)
        
        return None
    
    @staticmethod
    def _build_search_condition(table, search_term: str, search_fields: List[str]) -> Any:
        """构建搜索条件"""
        if not search_fields:
            return None
        
        search_conditions = []
        for field in search_fields:
            column = table.c.get(field)
            if column is not None:
                # 对于字符串类型的列，使用模糊搜索
                search_conditions.append(column.ilike(f"%{search_term}%"))
        
        if not search_conditions:
            return None
        
        return or_(*search_conditions)
    
    @staticmethod
    def _build_order_by(table, sort_fields: List[SortField]) -> List[Any]:
        """构建排序条件"""
        order_clauses = []
        for sort_field in sort_fields:
            column = table.c.get(sort_field.field)
            if column is not None:
                if sort_field.order == SortOrder.DESC:
                    order_clauses.append(column.desc())
                else:
                    order_clauses.append(column.asc())
        return order_clauses
    
    @staticmethod
    def _table_exists(db: Session, table_name: str) -> bool:
        """检查表是否存在"""
        try:
            # 使用SQLAlchemy的inspect来检查表是否存在
            from sqlalchemy import inspect
            inspector = inspect(db.get_bind())
            return table_name in inspector.get_table_names()
        except Exception:
            return False
    
    @staticmethod
    def get_table_columns(db: Session, table_name: str) -> List[Dict[str, Any]]:
        """获取表的列信息"""
        if not GenericQueryService._table_exists(db, table_name):
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"表 '{table_name}' 不存在"
            )
        
        metadata = MetaData()
        table = Table(table_name, metadata, autoload_with=db.get_bind())
        
        columns_info = []
        for column in table.columns:
            columns_info.append({
                'name': column.name,
                'type': str(column.type),
                'nullable': column.nullable,
                'primary_key': column.primary_key,
                'default': getattr(column.default, 'arg', None) if column.default else None
            })
        
        return columns_info

    @staticmethod
    def query_table_with_string(
        db: Session, 
        table_name: str, 
        condition_str: str,
        **kwargs
    ) -> QueryResponse:
        """
        使用SQL-like条件字符串查询表
        
        Args:
            db: 数据库会话
            table_name: 表名
            condition_str: SQL-like条件字符串，如 "age > 25 AND name LIKE '%John%'"
            **kwargs: 其他GenericFilter参数（search, search_fields, sort_by, page, page_size, include_total）
            
        Returns:
            QueryResponse: 查询结果
            
        Raises:
            ValueError: 如果条件字符串格式错误
            HTTPException: 如果表不存在或查询失败
        """
        try:
            # 解析条件字符串为GenericFilter对象
            filter_obj = parse_to_generic_filter(condition_str, **kwargs)
            
            # 使用现有的查询方法
            return GenericQueryService.query_table(db, table_name, filter_obj)
            
        except ValueError as e:
            logger.error(f"条件字符串解析失败: {condition_str}, 错误: {e}")
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"条件字符串格式错误: {str(e)}"
            )
        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"使用字符串条件查询表 {table_name} 失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail=f"查询失败: {str(e)}"
            )